﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using EntityModel;

namespace DataAccess
{
    public class ExecuteHelper<T, U> : ExecuteHelperBase<T, U>
        where T : EntityBase, new()
        where U : UserModel
    {
        struct ConnectionInfo
        {
            public string Server;
            public string Database;
            public string UserID;
            public string Password;
        }

        public override T Insert(T entity, U UserInfo)
        {
            if (!this.IsAuthorized<T>(UserInfo)) return null;

            entity.CreatedBy = UserInfo.Key;
            entity.CreatedDate = DateTime.Now;
            entity.UpdatedBy = UserInfo.Key;
            entity.UpdatedDate = entity.CreatedDate;

            this.objContext.Set<T>().Add(entity);
            this.objContext.SaveChanges();

            return entity;
        }

        public override T Update(T entity, U UserInfo)
        {
            if (!this.IsAuthorized<T>(UserInfo)) return null;

            var objEntity = this.objContext.Set<T>().Find(entity.Key);
            if (objEntity != null)
            {
                entity.UpdatedBy = UserInfo.Key;
                entity.UpdatedDate = entity.CreatedDate;

                this.objContext.Entry<T>(objEntity).CurrentValues.SetValues(entity);
                this.objContext.SaveChanges();
            }

            return entity;
        }

        public override T Delete(T entity, U UserInfo)
        {
            return DeleteByKey(entity.Key, UserInfo);
        }

        public override T DeleteByKey(Int32 key, U UserInfo)
        {
            if (!this.IsAuthorized<T>(UserInfo)) return null;

            var objEntity = this.objContext.Set<T>().Find(key);
            if (objEntity != null)
            {
                this.objContext.Set<T>().Remove(objEntity);
                this.objContext.SaveChanges();
            }

            return objEntity;
        }

        public override Result<T> ExecuteQuery(string procedureName, U UserInfo, params ObjectParameter[] parameters)
        {
            var result = new Result<T>();
                        
            if (!this.IsAuthorized<T>(UserInfo)) return result;

            if (parameters == null) parameters = new ObjectParameter[0];
            var objParamList = parameters as object;
            ShrinkParameters(procedureName, ref objParamList, false);
            parameters = objParamList as ObjectParameter[];

            ObjectContext context = ((IObjectContextAdapter)objContext).ObjectContext;
            result.EntityList = context.ExecuteFunction<T>(procedureName, parameters).ToList();

            var totalRecordParam = parameters.Where(p => p.Name.ToUpper() == "TOTALRECORDS").FirstOrDefault();
            if (totalRecordParam != null)
                result.TotalRecords = Convert.ToInt32(totalRecordParam.Value);
            else
                result.TotalRecords = result.EntityList.Count;

            return result;
        }

        public override Result<H> ExecuteQuery<H>(string procedureName, U UserInfo, params ObjectParameter[] parameters)
        {
            var result = new Result<H>();

            if (!this.IsAuthorized<H>(UserInfo)) return result;

            if (parameters == null) parameters = new ObjectParameter[0];
            var objParamList = parameters as object;
            ShrinkParameters(procedureName, ref objParamList, false);
            parameters = objParamList as ObjectParameter[];

            ObjectContext context = ((IObjectContextAdapter)objContext).ObjectContext;
            result.EntityList = context.ExecuteFunction<H>(procedureName, parameters).ToList();

            var totalRecordParam = parameters.Where(p => p.Name.ToUpper() == "TOTALRECORDS").FirstOrDefault();
            if (totalRecordParam != null)
                result.TotalRecords = Convert.ToInt32(totalRecordParam.Value);
            else
                result.TotalRecords = result.EntityList.Count;

            return result;
        }

        public override Result<H> ExecuteDynamic<H>(string procedureName, U UserInfo, params SqlParameter[] parameters)
        {
            var result = ExecuteQueryToDataRow(procedureName, UserInfo, parameters);
            var resultData = new Result<H>();
            var proList = typeof(H).GetProperties().Select(p => p.Name).ToList();

            resultData.TotalRecords = result.TotalRecords;
            resultData.EntityList = new List<H>();

            H objResult = null;
            result.EntityList.ForEach(item =>
            {
                objResult = new H();

                proList.ForEach(p =>
                {
                    if (item.Table.Columns.Contains(p))
                    {
                        if (!(item[p] is DBNull)) objResult.GetType().GetProperty(p).SetValue(objResult, item[p]);
                    }
                });

                resultData.EntityList.Add(objResult);
            });

            return resultData;
        }

        public override Result<DataRow> ExecuteQueryToDataRow(string procedureName, U UserInfo, params SqlParameter[] parameters)
        {
            //initialize dataset to contain data
            DataSet dsResult = new DataSet();

            if (parameters == null) parameters = new SqlParameter[0];
            var objParamList = parameters as object;
            ShrinkParameters(procedureName, ref objParamList, true);
            parameters = objParamList as SqlParameter[];

            //initialize connection to database
            var connectionString = ConnectionStringParser(this.objContext.Database.Connection.ConnectionString);
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder
            {
                DataSource = connectionString.Server,
                InitialCatalog = connectionString.Database,
                IntegratedSecurity = true,
                UserID = connectionString.UserID,
                Password = connectionString.Password
            };

            //open connection, execute store procedure and fill data to dataset
            SqlConnection con = new SqlConnection(sqlBuilder.ToString());
            {
                con.Open();

                SqlCommand command = new SqlCommand
                {
                    CommandType = CommandType.StoredProcedure,
                    CommandText = procedureName,
                    Connection = con,
                    CommandTimeout = Common.Variables.TimeOut
                };
                command.Parameters.AddRange(parameters);

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dsResult);

                adapter = null;
                command = null;
                con.Close();
                con = null;
            }

            //fill data to result object
            var result = new Result<DataRow>();
            result.EntityList = dsResult.Tables[0].Rows.Cast<DataRow>().ToList();
            var totalRecordParam = parameters.Where(p => p.ParameterName.ToUpper() == "TOTALRECORDS").FirstOrDefault();
            if (totalRecordParam != null)
                result.TotalRecords = Convert.ToInt32(totalRecordParam.Value);
            else
                result.TotalRecords = result.EntityList.Count;

            //return the result
            return result;
        }

        public override bool ExecuteNonQuery(string procedureName, U UserInfo, params SqlParameter[] parameters)
        {
            try
            {
                var result = this.objContext.Database.ExecuteSqlCommand(procedureName, parameters);

                return true;
            }
            catch
            {
                return false;
            }
        }

        public override int ExecuteScalar(string procedureName, U UserInfo, params SqlParameter[] parameters)
        {
            if (parameters == null) parameters = new SqlParameter[0];

            ObjectContext context = ((IObjectContextAdapter)objContext).ObjectContext;

            var result = context.ExecuteStoreCommand(procedureName, parameters);

            return result;
        }

        #region "private methods"

        private MethodInfo GetFuncInfoAtContext(string functionName)
        {
            return typeof(WRSEntities).GetMethod(functionName);
        }

        private ConnectionInfo ConnectionStringParser(string entityConnectionString)
        {
            var propertyList = entityConnectionString.Split(";".ToCharArray());
            var result = new ConnectionInfo();

            //server name
            var serverName = propertyList.Where(t => t.StartsWith("data source=")).FirstOrDefault();
            if (!string.IsNullOrEmpty(serverName))
            {
                result.Server = serverName.Replace("data source=", "");
            }

            //database name
            var databaseName = propertyList.Where(t => t.StartsWith("initial catalog=")).FirstOrDefault();
            if (!string.IsNullOrEmpty(databaseName))
            {
                result.Database = databaseName.Replace("initial catalog=", "");
            }

            //user id
            var userID = propertyList.Where(t => t.StartsWith("user id=")).FirstOrDefault();
            if (!string.IsNullOrEmpty(databaseName))
            {
                result.UserID = userID.Replace("user id=", "");
            }

            //password
            var password = propertyList.Where(t => t.StartsWith("password=")).FirstOrDefault();
            if (!string.IsNullOrEmpty(databaseName))
            {
                result.Password = password.Replace("password=", "");
            }

            return result;
        }

        private List<ProcedureModel> GetFuncInfoAtDatabase(string proName)
        {
            if (string.IsNullOrEmpty(proName)) return null;

            var strSQL = string.Format(Common.Variables.PROC_METADATA_SQL, proName);

            return this.objContext.Database.SqlQuery<ProcedureModel>(strSQL).ToList();
        }

        private bool ShrinkParameters(string proName, ref object parameters, bool isDynamic = false)
        {
            bool returnValue = true;

            if (!isDynamic)
            {
                var methodInfo = GetFuncInfoAtContext(proName);
                var paramList = (parameters as ObjectParameter[]).ToList();
                var paramNameProvideList = paramList.Select(p => p.Name.ToUpper()).ToList();
                var paramNameBaseList = methodInfo.GetParameters().Select(p => p.Name.ToUpper()).ToList();
                var missingParamList = paramNameBaseList.Where(t => !paramNameProvideList.Contains(t)).ToList();

                missingParamList.ForEach(t =>
                    {
                        var paramBase = methodInfo.GetParameters().FirstOrDefault(p => p.Name.ToUpper() == t);
                        if (paramBase.IsOut)
                            paramList.Add(new ObjectParameter(t, paramBase.ParameterType));
                        else if (paramBase.HasDefaultValue)
                            paramList.Add(new ObjectParameter(t, paramBase.ParameterType) { Value = paramBase.DefaultValue });
                        else
                        {
                            returnValue = false;
                            return;
                        }
                    });
                if (!returnValue) return returnValue;

                missingParamList = paramNameProvideList.Where(p => !paramNameBaseList.Contains(p)).ToList();
                paramList.RemoveAll(p => missingParamList.Contains(p.Name.ToUpper()));
                parameters = paramList.ToArray();
            }
            else
            {
                var procedureMetadata = GetFuncInfoAtDatabase(proName);
                var paramList = (parameters as SqlParameter[]).ToList();
                var paramNameProvideList = paramList.Select(p => p.ParameterName.ToUpper()).ToList();
                var paramNameBaseList = procedureMetadata.Select(p => p.ParamName.ToUpper()).ToList();
                var missingParamList = paramNameBaseList.Where(t => !paramNameProvideList.Contains(t)).ToList();

                missingParamList.ForEach(t =>
                {
                    var paramBase = procedureMetadata.FirstOrDefault(p => p.ParamName.ToUpper() == t);
                    SqlDbType paramType = (SqlDbType)Enum.Parse(typeof(SqlDbType), paramBase.DataType, true);

                    if (paramBase.IsOutput)
                        paramList.Add(new SqlParameter(t, paramType) { Direction = ParameterDirection.Output });
                    else
                        paramList.Add(new SqlParameter(t, paramType));
                });

                missingParamList = paramNameProvideList.Where(p => !paramNameBaseList.Contains(p)).ToList();
                paramList.RemoveAll(p => missingParamList.Contains(p.ParameterName));
                parameters = paramList.ToArray();
            }

            return returnValue;
        }

        #endregion
    }
}
